/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package framework.dal;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import framework.groups.Group;
import framework.groups.Item;

/**
 *
 * @author Guilherme
 */
public class GroupDB
{
    
            
    public static ArrayList<Group> getAll()
    {
        ArrayList<Group> groups = new ArrayList<>();

        ResultSet rs = Link.select("grupo", "*", "true");
        try
        {
            while (rs.next())
            {
                Integer id = rs.getInt("idgrupo");
                String groupName = rs.getString("nome");
                Integer groupInteger = new Integer(Integer.parseInt(rs.getString("idGrupo")));
                ArrayList<Item> notes = ItemDB.getAll(groupInteger);
                
                Group g = new Group(id, groupName, notes);
                groups.add(g);
            }
        } catch (SQLException ex)
        {
            return null;
        }
        
        return groups;
    }
    
    public static Integer getLastInteger()
    {
        ResultSet rs = Link.select("grupo", "MAX(idGrupo), nome", "true");
        try
        {
            rs.next();
            return new Integer(rs.getInt("MAX(idGrupo)"));
            
        } catch (SQLException ex)
        {
            return null;
        }
    }
    
    public static boolean insert(Group g, Integer id)
    {
        return Link.add("grupo", "(" + id + ", 0,'" + g.getNameGroup() + "')");
    }
    
    public static boolean insert(String nameGroup, Integer id)
    {
        return Link.add("grupo", "(" + id + ", '" + nameGroup + "')");
    }
    
    public static boolean deleteItem(Group g, Integer id)
    {
        return Link.delete("grupo", "idGrupo = " + g.getIdentifier() + " AND idItem = " + id);
        
    }
    
    public static boolean deleteGroup(String nameGroup)
    {
        return Link.delete("grupo", "nome = '"+ nameGroup + "'");
    }
    
    public static boolean newGroup(String nameGroup)
    {
        String s = "INSERT INTO notes.grupo VALUES(" + (getLastInteger() + 1) + ", '" + nameGroup +"')";
        return Link.executeUpdate(s);
    }
    
    public static Integer getGroupID(String nameGroup)
    {
        String s = "SELECT * FROM notes.grupo g WHERE g.nome = '" + nameGroup + "'";
        Integer groupID = null;
        ResultSet res = Link.executeQuery(s);
        try
        {
            while(res.next())
            {
                groupID = res.getInt("idgrupo");
            }
        } catch (SQLException ex)
        {
            return null;
        }
        if (groupID == null)
        {
            return null;
        }
        return groupID;
    }
    
    public static Group getGroup(String nameGroup)
    {
        Integer groupID = getGroupID(nameGroup);
        String s = "SELECT * FROM notes.itemgrupo ig JOIN notes.item i ON i.id = ig.idItem WHERE ig.idGrupo = " + groupID;
        ResultSet res = Link.executeQuery(s);
        ArrayList<Item> itens = new ArrayList<>();
        try
        {
            while(res.next())
            {
                Integer itemID = res.getInt("idItem");
                String des = res.getString("descr");
                Item i = new Item(itemID, des);
                itens.add(i);
            }
        } catch (SQLException ex)
        {
            return null;
        }
        ArrayList<Item> a = new ArrayList<>();
        return new Group(nameGroup, itens);
        
    }
 }
